In multidimensional data analysis using OLAP (Online Analytical Processing), a data warehouse or a datamart is constructed on a relational database, and flexible totalizing operations viewed from various angles are performed by defining events serving as analytical viewpoints. In the totalizing operations, fineness or granularity of analysis is determined by the hierarchy of attributes used as the analytical viewpoints viewed from an application.
By way of example, when a report is to be analyzed from a viewpoint of the sales by products, three analysis granularities, “sales by primary classification groups”, “sales by medium classification groups” and “sales by product codes”, form a viewpoint hierarchy. Such a hierarchy is called a “dimension hierarchy” in the multi-dimensional data analysis because a master table for storing product information used as attributes is called a “dimension table”.
When an analysis application that employs a data warehouse or a datamart is to be constructed, a system for easily constructing a dimension hierarchy or a multi-dimension data model is very important for a data warehouse administrator.
Typically, a dimensional hierarchy is constructed as follows.
(1) A DBA (database administrator) selects relevant dimension tables from a list of tables in a database.
(2) The DBA selects, for each of the selected tables, a column required for a dimension hierarchy.
(3) For the selected columns (attributes), the DBA is cognizant of the hierarchical relationship based, for example, on the concept obtained from the attributes, and manually adds a table and a column to a database and a table, respectively, that are currently constructed, in order from the highest level to the lowest level in the dimension hierarchy.
Japanese Published Patent Application No.2000-194710 (Patent Document 1) discloses a system for coupling two tree structures for multidimensional database processing. As explained with reference to FIGS. 9 to 15 in Patent Document 1, according to this system, overlapping categories in the two tree structures before coupling are merged, following a predetermined rule, to obtain a single category for inclusion in a tree structure after coupling. This prevents two or more identical categories from existing in a tree structure.
Japanese Published Patent Application No. 2000-20529 (Patent Document 2) discloses a data warehouse test data generator in which a link key table is prepared based on a link key read from a dimension table (a master table) coupled with a fact table for use in the generation of test data.
Japanese Published Patent Application No.2002-328937 (Patent Document 3) discloses a method for analyzing a large volume of accumulated data in which the patient rule inductive method (PRIM) is employed to find a high average value area within a set of a huge number of data records, and intensive analysis is performed for this area by using an OLAP tool to increase the efficiency of the analysis.
As described above, a DBA is cognizant of the hierarchical relationship of individual columns in a dimension table based on the concept of attributes that serve as columns, or is cognizant of the chain relationship of dimension tables under the referential integrity constraint, and manually derives the dimension hierarchy in accordance with the obtained relationship. That is, since a high-level process, such as cognizance of the concept, is required to derive the dimension hierarchy, it is difficult for such an operation to be computerized to derive the dimension hierarchy of individual columns in a dimension table that has been denormalized.
While Patent Documents 1 to 3 disclose the multidimensional database processing, combination of fact and dimension tables, and use of the OLAP tool, they do not suggest an advantageous method for automating a process for deriving the dimension hierarchy from a dimension table that has been denormalized.